﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using LotteryVote.Entity;

namespace LotteryVote.Data
{
    public class LotteryResultDataAccess : DataBase
    {
        public void InsertPeriod(LotteryPeriod period)
        {
            string sql = string.Format(@"INSERT INTO {0} ({1}) VALUES (@{1}) SELECT IDENT_CURRENT('{0}')", LotteryPeriod.TABLENAME, LotteryPeriod.COMPANYID);
            object id = base.ExecuteScalar(sql, new SqlParameter(LotteryPeriod.COMPANYID, period.Company.CompanyId));
            period.PeriodId = Convert.ToInt32(id);
        }
        public void InsertRecord(LotteryRecord record)
        {
            string sql = string.Format(@"INSERT INTO {0} ({1},{2}) VALUES (@{1},@{2}) SELECT IDENT_CURRENT('{0}')", LotteryRecord.TABLENAME, LotteryRecord.VALUE,
                LotteryRecord.PERIODID);
            object id = base.ExecuteScalar(sql, new SqlParameter(LotteryRecord.VALUE, record.Value),
                new SqlParameter(LotteryRecord.PERIODID, record.PeriodId));
            record.RecordId = Convert.ToInt32(id);
        }

        public void DeleteRecord(LotteryPeriod period)
        {
            string sql = string.Format(@"DELETE {0} WHERE {1}=@{1}", LotteryRecord.TABLENAME, LotteryRecord.PERIODID);
            base.ExecuteNonQuery(sql, new SqlParameter(LotteryRecord.PERIODID, period.PeriodId));
        }

        public LotteryPeriod GetPeriod(LotteryCompany company, DateTime date)
        {
            string sql = string.Format(@"SELECT * FROM {0} WHERE {1}=@{1} AND DAY({2})=DAY(@{2})", LotteryPeriod.TABLENAME, LotteryPeriod.COMPANYID, LotteryPeriod.CREATEDATE);
            return base.ExecuteEntity<LotteryPeriod>(sql, new SqlParameter(LotteryPeriod.COMPANYID, company.CompanyId),
                new SqlParameter(LotteryPeriod.CREATEDATE, date));
        }

        public DataSet GetResultByDate(DateTime date)
        {
            string sql = string.Format(@"select * from tb_LotteryCompany AS LC
JOIN tb_LotteryPeriod LP ON LP.CompanyId=LC.CompanyId
JOIN tb_LotteryRecord LR ON LR.PeriodId=LP.PeriodId
WHERE DAY(LP.CreateDate)=DAY(@{0})", LotteryPeriod.CREATEDATE);
            return base.ExecuteDataSet(sql, new SqlParameter(LotteryPeriod.CREATEDATE, date));
        }
        public IList<LotteryRecord> ToRecordList(IEnumerable<DataRow> rows)
        {
            return base.ToEnties<LotteryRecord>(rows);
        }
        public LotteryCompany ToCompany(IEnumerable<DataRow> rows)
        {
            return base.ToEntity<LotteryCompany>(rows);
        }
        public IList<LotteryCompany> ToCompanyList(IEnumerable<DataRow> rows)
        {
            return base.ToEnties<LotteryCompany>(rows);
        }

        /// <summary>
        /// 根据公司和日期获取开奖结果.
        /// </summary>
        /// <param name="company">The company.</param>
        /// <param name="date">The date.</param>
        /// <returns></returns>
        public IList<LotteryRecord> ListRecord(LotteryCompany company, DateTime date)
        {
            string sql = string.Format(@"SELECT LR.* FROM {0} LR
JOIN {1} LP ON LP.{2}=LR.{3}
WHERE DAY(LP.{4})=DAY(@{4}) AND LP.{5}=@{5}", LotteryRecord.TABLENAME,
            LotteryPeriod.TABLENAME, LotteryPeriod.PERIODID, LotteryRecord.PERIODID, LotteryPeriod.CREATEDATE, LotteryPeriod.COMPANYID);
            return base.ExecuteList<LotteryRecord>(sql, new SqlParameter(LotteryPeriod.CREATEDATE, date),
                new SqlParameter(LotteryPeriod.COMPANYID, company.CompanyId));
        }
    }
}
